﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using VK.Comm;
using VK.Comm.TransactSql;

namespace VK.Casemeet.Magazines
{
    internal static class MagazineDAL
    {
        internal static MagazineEntity GetMagazineByReader(IDataReader dr)
        {
            MagazineEntity magazine = new MagazineEntity();

            magazine.MagazineID = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
            magazine.Title = dr.IsDBNull(1) ? null : dr.GetString(1);
            magazine.FileUrl = dr.IsDBNull(2) ? null : dr.GetString(2);
            magazine.Created = Util.ConverDateTime(dr.GetValue(3));
            magazine.Sort = dr.IsDBNull(4) ? 0 : dr.GetInt32(4);
            magazine.IsEnable = dr.IsDBNull(5) ? false : dr.GetBoolean(5);
            magazine.ImgUrl = dr.IsDBNull(6) ? null : dr.GetString(6);

            return magazine;
        }

        internal static int GetMagazineCount(IList<Condition> conditionArray)
        {
            Database db = DatabaseFactory.CreateDatabase();
            int result = 0;

            string SelectMagazine = @"SELECT COUNT([MagazineID]) FROM [Magazine]";

            List<DbParameter> parameterlst = new List<DbParameter>();
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectMagazine, ref parameterlst);
            DbCommand SelectMagazineCommand = db.GetSqlStringCommand(SelectMagazine);
            DbHelper.PackDbCommandByDbParameters(SelectMagazineCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(SelectMagazineCommand);
            result = obj == null ? 0 : int.Parse(obj.ToString());

            return result;
        }

        internal static List<MagazineEntity> GetMagazineList(int startRecordIndex, int endRecordIndex, IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<MagazineEntity> magazinelst = new List<MagazineEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectMagazine = @"
                    SELECT [MagazineID],[Title],[FileUrl],[Created],[Sort],[IsEnable],[ImgUrl]
                    FROM (
                        SELECT [Magazine].[MagazineID],[Magazine].[Title],[Magazine].[FileUrl],[Magazine].[Created],
                            [Magazine].[Sort],[Magazine].[IsEnable],[Magazine].[ImgUrl],
                            ROW_NUMBER() OVER (ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectMagazine += " " + sortField + " " + sortMode + ",";
            SelectMagazine += @" [Magazine].[MagazineID] Desc
                    ) AS rowNum 
                        FROM [Magazine] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectMagazine, ref parameterlst);
            SelectMagazine += @"
                    ) AS [Magazine] 
                    WHERE rowNum BETWEEN @StartRecordIndex and @EndRecordIndex ";

            DbCommand SelectMagazineCommand = db.GetSqlStringCommand(SelectMagazine);
            parameterlst.Add(DbHelper.PackInParameter(db, "@StartRecordIndex", DbType.Int32, startRecordIndex));
            parameterlst.Add(DbHelper.PackInParameter(db, "@EndRecordIndex", DbType.Int32, endRecordIndex));
            DbHelper.PackDbCommandByDbParameters(SelectMagazineCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectMagazineCommand))
            {
                while (dr.Read())
                {
                    magazinelst.Add(GetMagazineByReader(dr));
                }
            }

            return magazinelst;
        }

        internal static List<MagazineEntity> GetMagazineList(IList<Condition> conditionArray, string sortField, SortMode sortMode)
        {
            Database db = DatabaseFactory.CreateDatabase();
            List<MagazineEntity> magazinelst = new List<MagazineEntity>();

            List<DbParameter> parameterlst = new List<DbParameter>();
            string SelectMagazine = @"
                    SELECT [Magazine].[MagazineID],[Magazine].[Title],[Magazine].[FileUrl],[Magazine].[Created],
                        [Magazine].[Sort],[Magazine].[IsEnable],[Magazine].[ImgUrl],
                    FROM [Magazine] ";
            ConditionProcess.DisposeCommandByCondition(db, conditionArray, ref SelectMagazine, ref parameterlst);
            SelectMagazine += " ORDER BY ";
            if (!string.IsNullOrEmpty(sortField)) SelectMagazine += " " + sortField + " " + sortMode + ",";
            SelectMagazine += " [Magazine].[MagazineID] Desc ";

            DbCommand SelectMagazineCommand = db.GetSqlStringCommand(SelectMagazine);
            DbHelper.PackDbCommandByDbParameters(SelectMagazineCommand, parameterlst.ToArray());

            using (IDataReader dr = db.ExecuteReader(SelectMagazineCommand))
            {
                while (dr.Read())
                {
                    magazinelst.Add(GetMagazineByReader(dr));
                }
            }

            return magazinelst;
        }

        internal static MagazineEntity GetMagazine(int magazineID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            MagazineEntity magazine = null;

            string SelectMagazine = @"
                    SELECT [Magazine].[MagazineID],[Magazine].[Title],[Magazine].[FileUrl],[Magazine].[Created],
                        [Magazine].[Sort],[Magazine].[IsEnable],[Magazine].[ImgUrl]
                    FROM [Magazine] 
                    WHERE [Magazine].[MagazineID]=@MagazineID
                ";

            DbCommand SelectMagazineCommand = db.GetSqlStringCommand(SelectMagazine);
            db.AddInParameter(SelectMagazineCommand, "@MagazineID", DbType.Int32, magazineID);

            using (IDataReader dr = db.ExecuteReader(SelectMagazineCommand))
            {
                if (dr.Read())
                {
                    magazine = GetMagazineByReader(dr);
                }
            }

            return magazine;
        }

        internal static int InsertMagazine(MagazineEntity magazine)
        {
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder InsertMagazine = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            InsertMagazine.Append(@"Declare @MagazineID int;
                    INSERT INTO [Magazine] 
				([Title],[FileUrl],[Created],[Sort],[IsEnable],[ImgUrl])
				 VALUES (@Title,@FileUrl,@Created,@Sort,@IsEnable,@ImgUrl);
                    SET @MagazineID=@@IDENTITY;
                ");

            parameterlst.Add(DbHelper.PackInParameter(db, "@Title", DbType.String, magazine.Title));
            parameterlst.Add(DbHelper.PackInParameter(db, "@FileUrl", DbType.String, magazine.FileUrl));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl", DbType.String, magazine.ImgUrl));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, magazine.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, magazine.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, magazine.IsEnable));


            InsertMagazine.Append("SELECT @MagazineID;");

            DbCommand InsertMagazineCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_InsertMagazine", InsertMagazine.ToString()));
            DbHelper.PackDbCommandByDbParameters(InsertMagazineCommand, parameterlst.ToArray());

            object obj = db.ExecuteScalar(InsertMagazineCommand);

            return obj == null ? 0 : (int)obj;
        }

        internal static int UpdateMagazine(MagazineEntity magazine)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            StringBuilder UpdateMagazine = new StringBuilder();
            List<DbParameter> parameterlst = new List<DbParameter>();

            UpdateMagazine.Append(@"UPDATE [Magazine] 
				 SET [Title]=@Title,[FileUrl]=@FileUrl,[Created]=@Created,[Sort]=@Sort,[IsEnable]=@IsEnable,
                    [ImgUrl]=@ImgUrl
				 WHERE [MagazineID] = @MagazineID;");

            parameterlst.Add(DbHelper.PackInParameter(db, "@MagazineID", DbType.Int32, magazine.MagazineID));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Title", DbType.String, magazine.Title));
            parameterlst.Add(DbHelper.PackInParameter(db, "@ImgUrl", DbType.String, magazine.ImgUrl));
            parameterlst.Add(DbHelper.PackInParameter(db, "@FileUrl", DbType.String, magazine.FileUrl));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Created", DbType.DateTime, magazine.Created));
            parameterlst.Add(DbHelper.PackInParameter(db, "@Sort", DbType.Int32, magazine.Sort));
            parameterlst.Add(DbHelper.PackInParameter(db, "@IsEnable", DbType.Boolean, magazine.IsEnable));


            DbCommand UpdateMagazineCommand = db.GetSqlStringCommand(WrapSql.WrapTransaction("Tran_UpdateMagazine", UpdateMagazine.ToString()));
            DbHelper.PackDbCommandByDbParameters(UpdateMagazineCommand, parameterlst.ToArray());

            result = db.ExecuteNonQuery(UpdateMagazineCommand);

            return result;
        }

        internal static int DeleteMagazine(int magazineID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string DeleteMagazine = @"
                DELETE FROM [Magazine] WHERE MagazineID=@MagazineID";

            DbCommand DeleteMagazineCommand = db.GetSqlStringCommand(DeleteMagazine);
            db.AddInParameter(DeleteMagazineCommand, "@MagazineID", DbType.Int32, magazineID);
            result = db.ExecuteNonQuery(DeleteMagazineCommand);

            return result;
        }

        internal static int SetMagazineEnable(int magazineID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetMagazine = @"UPDATE [Magazine]
                    SET [IsEnable] = ([IsEnable]+1)%2
                    WHERE [MagazineID] = @MagazineID";

            DbCommand SetMagazineCommand = db.GetSqlStringCommand(SetMagazine);
            db.AddInParameter(SetMagazineCommand, "@MagazineID", DbType.Int32, magazineID);
            result = db.ExecuteNonQuery(SetMagazineCommand);

            return result;
        }

        internal static int HitMagazine(int magazineID)
        {
            int result = 0;
            Database db = DatabaseFactory.CreateDatabase();

            string SetMagazine = @"UPDATE [Magazine]
                    SET [Hits] = [Hits]+1
                    WHERE [MagazineID] = @MagazineID";

            DbCommand SetMagazineCommand = db.GetSqlStringCommand(SetMagazine);
            db.AddInParameter(SetMagazineCommand, "@MagazineID", DbType.Int32, magazineID);
            result = db.ExecuteNonQuery(SetMagazineCommand);

            return result;
        }
    }
}
